<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <meta name="generator" content="VuePress 2.0.0-beta.60">
    <script>
      (function() {
        const userMode = localStorage.getItem('vuepress-reco-color-scheme') || 'auto';
        const systemDarkMode = window.matchMedia && window.matchMedia('(prefers-color-scheme: dark)').matches;

        if (userMode === 'dark' || (userMode === 'auto' && systemDarkMode)) {
          document.documentElement.classList.toggle('dark', true);
        }
      })();
    </script>
    <title>MySql 函数 | 阿派 | Apai Blog</title><meta name="description" content="Just playing around">
    <link rel="modulepreload" href="/assets/app-8a43a0f0.js"><link rel="modulepreload" href="/assets/framework-e1bed10d.js"><link rel="modulepreload" href="/assets/MySqlFunction.html-2c432c8c.js"><link rel="modulepreload" href="/assets/MySqlFunction.html-23affe6f.js"><link rel="prefetch" href="/assets/index.html-ef2f4f01.js" as="script"><link rel="prefetch" href="/assets/index.html-1013d654.js" as="script"><link rel="prefetch" href="/assets/index.html-a146dedc.js" as="script"><link rel="prefetch" href="/assets/index.html-f4b2eaea.js" as="script"><link rel="prefetch" href="/assets/index.html-e3ed352e.js" as="script"><link rel="prefetch" href="/assets/index.html-42ce2ab9.js" as="script"><link rel="prefetch" href="/assets/index.html-ec24ec9e.js" as="script"><link rel="prefetch" href="/assets/index.html-d3d8d7ce.js" as="script"><link rel="prefetch" href="/assets/index.html-9faf073b.js" as="script"><link rel="prefetch" href="/assets/index.html-9364b7dd.js" as="script"><link rel="prefetch" href="/assets/index.html-fc0fb462.js" as="script"><link rel="prefetch" href="/assets/index.html-603c6c19.js" as="script"><link rel="prefetch" href="/assets/index.html-0349deb8.js" as="script"><link rel="prefetch" href="/assets/index.html-09405a87.js" as="script"><link rel="prefetch" href="/assets/index.html-91577c59.js" as="script"><link rel="prefetch" href="/assets/index.html-552ae255.js" as="script"><link rel="prefetch" href="/assets/index.html-424369c4.js" as="script"><link rel="prefetch" href="/assets/index.html-1dfc0069.js" as="script"><link rel="prefetch" href="/assets/index.html-ec62f595.js" as="script"><link rel="prefetch" href="/assets/index.html-4621ef22.js" as="script"><link rel="prefetch" href="/assets/index.html-e189ec13.js" as="script"><link rel="prefetch" href="/assets/index.html-b424a45b.js" as="script"><link rel="prefetch" href="/assets/index.html-623952a8.js" as="script"><link rel="prefetch" href="/assets/index.html-cedcb7c8.js" as="script"><link rel="prefetch" href="/assets/index.html-07f61d2f.js" as="script"><link rel="prefetch" href="/assets/index.html-9ef01f1e.js" as="script"><link rel="prefetch" href="/assets/index.html-dfa6d19f.js" as="script"><link rel="prefetch" href="/assets/index.html-23dd0955.js" as="script"><link rel="prefetch" href="/assets/index.html-1e7a4ff3.js" as="script"><link rel="prefetch" href="/assets/index.html-e12f154a.js" as="script"><link rel="prefetch" href="/assets/index.html-30de2d02.js" as="script"><link rel="prefetch" href="/assets/index.html-6a244983.js" as="script"><link rel="prefetch" href="/assets/index.html-8900b10e.js" as="script"><link rel="prefetch" href="/assets/index.html-b3210461.js" as="script"><link rel="prefetch" href="/assets/biaoqian-fenlei-moban.html-2db9524d.js" as="script"><link rel="prefetch" href="/assets/MuBan-Apai.html-5af383c8.js" as="script"><link rel="prefetch" href="/assets/guide.html-5aaed80e.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-82ee5401.js" as="script"><link rel="prefetch" href="/assets/kaishi.html-9d8493a0.js" as="script"><link rel="prefetch" href="/assets/Hobby-SheYing.html-3ac28a1b.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-96d10c31.js" as="script"><link rel="prefetch" href="/assets/Work-MaYun.html-5e497e8c.js" as="script"><link rel="prefetch" href="/assets/Java-basics.html-4a44925e.js" as="script"><link rel="prefetch" href="/assets/JavaAPI.html-ae8a1843.js" as="script"><link rel="prefetch" href="/assets/JavaBiKeng04.html-c9e75b16.js" as="script"><link rel="prefetch" href="/assets/JavaGongNeng03.html-6ca97faf.js" as="script"><link rel="prefetch" href="/assets/JavaJinJie02.html-eaabb81d.js" as="script"><link rel="prefetch" href="/assets/JavaMyUtil.html-e2cff404.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlus.html-b608a85b.js" as="script"><link rel="prefetch" href="/assets/MySql.html-4bcdc408.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ.html-2cc19b22.js" as="script"><link rel="prefetch" href="/assets/Redis.html-f7a014e9.js" as="script"><link rel="prefetch" href="/assets/ShiWuAffair.html-33f5c0c4.js" as="script"><link rel="prefetch" href="/assets/Spring.html-628746d6.js" as="script"><link rel="prefetch" href="/assets/SpringConfig.html-49f3c621.js" as="script"><link rel="prefetch" href="/assets/SpringPeiZhi.html-ac6df88a.js" as="script"><link rel="prefetch" href="/assets/SpringSecurity.html-057633f1.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-57d6fc97.js" as="script"><link rel="prefetch" href="/assets/DockerCompose.html-98ad6b52.js" as="script"><link rel="prefetch" href="/assets/Linux.html-43351365.js" as="script"><link rel="prefetch" href="/assets/Nginx.html-f1276de6.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ_COPY.html-5cdb6370.js" as="script"><link rel="prefetch" href="/assets/Redis_COPY.html-486e1d26.js" as="script"><link rel="prefetch" href="/assets/Redis_CRUD.html-47e0af0f.js" as="script"><link rel="prefetch" href="/assets/BatJiaoBen.html-d2d2666a.js" as="script"><link rel="prefetch" href="/assets/DmShengChen.html-bb069d8e.js" as="script"><link rel="prefetch" href="/assets/EasyExcel.html-b1eb613d.js" as="script"><link rel="prefetch" href="/assets/GitLu.html-eae89aa5.js" as="script"><link rel="prefetch" href="/assets/JavaKaiFa.html-4fbb86cc.js" as="script"><link rel="prefetch" href="/assets/JSR303.html-b976b418.js" as="script"><link rel="prefetch" href="/assets/MinIO.html-5e3d4702.js" as="script"><link rel="prefetch" href="/assets/ShardingJDBC.html-d0f811b3.js" as="script"><link rel="prefetch" href="/assets/Swagger.html-bb111a21.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-994cf672.js" as="script"><link rel="prefetch" href="/assets/Angular.html-3115afab.js" as="script"><link rel="prefetch" href="/assets/ElementUI.html-35bc6477.js" as="script"><link rel="prefetch" href="/assets/Html-Js.html-015f1806.js" as="script"><link rel="prefetch" href="/assets/Vue-basics.html-a64846f4.js" as="script"><link rel="prefetch" href="/assets/Vue3_group.html-f53be77f.js" as="script"><link rel="prefetch" href="/assets/Vue3_option.html-19f76b9e.js" as="script"><link rel="prefetch" href="/assets/ApifoxAPIceshi.html-19cd8153.js" as="script"><link rel="prefetch" href="/assets/PDManeryuanshujianmo.html-f6fa9d9e.js" as="script"><link rel="prefetch" href="/assets/OCRwenzishibie.html-11fc474d.js" as="script"><link rel="prefetch" href="/assets/EasyExcelbiaogeku.html-30777927.js" as="script"><link rel="prefetch" href="/assets/Javadaimashengchengqi.html-cf566ce0.js" as="script"><link rel="prefetch" href="/assets/JDKshuangbanben.html-1f259d3b.js" as="script"><link rel="prefetch" href="/assets/JSR303canshuxiaoyan.html-78322130.js" as="script"><link rel="prefetch" href="/assets/Mail  youjian.html-7e3a0f69.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlusxiangjie.html-0168c152.js" as="script"><link rel="prefetch" href="/assets/MySQLzhucongfuzhi.html-7a4739e8.js" as="script"><link rel="prefetch" href="/assets/Redis-API.html-f488940c.js" as="script"><link rel="prefetch" href="/assets/SpringBootshiwu.html-c07a3864.js" as="script"><link rel="prefetch" href="/assets/SpringBootduxiefenli.html-f0993780.js" as="script"><link rel="prefetch" href="/assets/Springguolvqihelanjieqi.html-78feb321.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-caecf367.js" as="script"><link rel="prefetch" href="/assets/quanjuyichang-JSR303.html-add01eff.js" as="script"><link rel="prefetch" href="/assets/Docker.html-14746447.js" as="script"><link rel="prefetch" href="/assets/Dockeryuanchengfangwenbushu.html-7dc4f8d3.js" as="script"><link rel="prefetch" href="/assets/Nginxfanxiangdaili.html-42533685.js" as="script"><link rel="prefetch" href="/assets/Apai-SheYingOne.html-143bc574.js" as="script"><link rel="prefetch" href="/assets/Apai-Year.html-b713587f.js" as="script"><link rel="prefetch" href="/assets/My2305.html-b224c5e2.js" as="script"><link rel="prefetch" href="/assets/My2306.html-94838726.js" as="script"><link rel="prefetch" href="/assets/My2307.html-df2118c4.js" as="script"><link rel="prefetch" href="/assets/My2308.html-396c79bc.js" as="script"><link rel="prefetch" href="/assets/My2309.html-1ebdc4bb.js" as="script"><link rel="prefetch" href="/assets/My2310.html-d4ffbd04.js" as="script"><link rel="prefetch" href="/assets/My2311.html-30f23133.js" as="script"><link rel="prefetch" href="/assets/My2312.html-2da62191.js" as="script"><link rel="prefetch" href="/assets/Yi-HuBei.html-90d02bf7.js" as="script"><link rel="prefetch" href="/assets/Yi-ZhuHai.html-e202f6fb.js" as="script"><link rel="prefetch" href="/assets/Apache ECharts tubiao.html-6d4a4703.js" as="script"><link rel="prefetch" href="/assets/Vue3 zuheshi API.html-9fe2887f.js" as="script"><link rel="prefetch" href="/assets/Vue3 xuanxiangshi API.html-d584c181.js" as="script"><link rel="prefetch" href="/assets/Vue3-i18n-guojihuachajian.html-16f6e650.js" as="script"><link rel="prefetch" href="/assets/Batpichulijiaoben.html-307223b0.js" as="script"><link rel="prefetch" href="/assets/Gitgongzuoliu.html-fa6e129f.js" as="script"><link rel="prefetch" href="/assets/Typoratuchuang.html-22c57156.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-df146bdb.js" as="script"><link rel="prefetch" href="/assets/My2401.html-49f6440f.js" as="script"><link rel="prefetch" href="/assets/My2402.html-950d4e85.js" as="script"><link rel="prefetch" href="/assets/shenhai-0509.html-2007281b.js" as="script"><link rel="prefetch" href="/assets/zhuhaixing.html-17db8380.js" as="script"><link rel="prefetch" href="/assets/404.html-f9875e7b.js" as="script"><link rel="prefetch" href="/assets/index.html-40bced6f.js" as="script"><link rel="prefetch" href="/assets/index.html-3c78780e.js" as="script"><link rel="prefetch" href="/assets/index.html-ef51ba1b.js" as="script"><link rel="prefetch" href="/assets/index.html-bdec80a5.js" as="script"><link rel="prefetch" href="/assets/index.html-3b5427f8.js" as="script"><link rel="prefetch" href="/assets/index.html-cbd49540.js" as="script"><link rel="prefetch" href="/assets/index.html-b0b97c81.js" as="script"><link rel="prefetch" href="/assets/index.html-2793d245.js" as="script"><link rel="prefetch" href="/assets/index.html-71d9a5d9.js" as="script"><link rel="prefetch" href="/assets/index.html-4e7d77cd.js" as="script"><link rel="prefetch" href="/assets/index.html-aeace11a.js" as="script"><link rel="prefetch" href="/assets/index.html-c62ed802.js" as="script"><link rel="prefetch" href="/assets/index.html-e640d31f.js" as="script"><link rel="prefetch" href="/assets/index.html-48e1c605.js" as="script"><link rel="prefetch" href="/assets/index.html-385dcb56.js" as="script"><link rel="prefetch" href="/assets/index.html-1bb1a523.js" as="script"><link rel="prefetch" href="/assets/index.html-1ed80e8d.js" as="script"><link rel="prefetch" href="/assets/index.html-2c154a45.js" as="script"><link rel="prefetch" href="/assets/index.html-3e93d436.js" as="script"><link rel="prefetch" href="/assets/index.html-2a93e2c9.js" as="script"><link rel="prefetch" href="/assets/index.html-cff07ba3.js" as="script"><link rel="prefetch" href="/assets/index.html-7ec32a34.js" as="script"><link rel="prefetch" href="/assets/index.html-ddb40108.js" as="script"><link rel="prefetch" href="/assets/index.html-40ab6c4f.js" as="script"><link rel="prefetch" href="/assets/index.html-47e4ee8b.js" as="script"><link rel="prefetch" href="/assets/index.html-c15250e1.js" as="script"><link rel="prefetch" href="/assets/index.html-fb2ea2e4.js" as="script"><link rel="prefetch" href="/assets/index.html-d7e5408a.js" as="script"><link rel="prefetch" href="/assets/index.html-a38055d0.js" as="script"><link rel="prefetch" href="/assets/index.html-be4e50cb.js" as="script"><link rel="prefetch" href="/assets/index.html-660e4bef.js" as="script"><link rel="prefetch" href="/assets/index.html-98aa61ea.js" as="script"><link rel="prefetch" href="/assets/index.html-a6b7472f.js" as="script"><link rel="prefetch" href="/assets/index.html-ab78917a.js" as="script"><link rel="prefetch" href="/assets/biaoqian-fenlei-moban.html-f2c6c309.js" as="script"><link rel="prefetch" href="/assets/MuBan-Apai.html-5714876d.js" as="script"><link rel="prefetch" href="/assets/guide.html-9a89b22b.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-f8203e0a.js" as="script"><link rel="prefetch" href="/assets/kaishi.html-4ff9d0f3.js" as="script"><link rel="prefetch" href="/assets/Hobby-SheYing.html-e1ab7a00.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-4b735204.js" as="script"><link rel="prefetch" href="/assets/Work-MaYun.html-a185f330.js" as="script"><link rel="prefetch" href="/assets/Java-basics.html-c0d3a063.js" as="script"><link rel="prefetch" href="/assets/JavaAPI.html-f75cbb54.js" as="script"><link rel="prefetch" href="/assets/JavaBiKeng04.html-d2fc755d.js" as="script"><link rel="prefetch" href="/assets/JavaGongNeng03.html-c51a3060.js" as="script"><link rel="prefetch" href="/assets/JavaJinJie02.html-e5a66660.js" as="script"><link rel="prefetch" href="/assets/JavaMyUtil.html-6e16ee98.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlus.html-1cafeb7e.js" as="script"><link rel="prefetch" href="/assets/MySql.html-5adc8c86.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ.html-d771200f.js" as="script"><link rel="prefetch" href="/assets/Redis.html-aa5c160a.js" as="script"><link rel="prefetch" href="/assets/ShiWuAffair.html-ce7fc825.js" as="script"><link rel="prefetch" href="/assets/Spring.html-3d622ed7.js" as="script"><link rel="prefetch" href="/assets/SpringConfig.html-6fe667f9.js" as="script"><link rel="prefetch" href="/assets/SpringPeiZhi.html-96b777da.js" as="script"><link rel="prefetch" href="/assets/SpringSecurity.html-fe792e2f.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-7fd7be86.js" as="script"><link rel="prefetch" href="/assets/DockerCompose.html-f6ef44c4.js" as="script"><link rel="prefetch" href="/assets/Linux.html-a1058183.js" as="script"><link rel="prefetch" href="/assets/Nginx.html-bf694d87.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ_COPY.html-1e53d902.js" as="script"><link rel="prefetch" href="/assets/Redis_COPY.html-ef853ef3.js" as="script"><link rel="prefetch" href="/assets/Redis_CRUD.html-7a4bb7d5.js" as="script"><link rel="prefetch" href="/assets/BatJiaoBen.html-20ec59d7.js" as="script"><link rel="prefetch" href="/assets/DmShengChen.html-92a2db1f.js" as="script"><link rel="prefetch" href="/assets/EasyExcel.html-ac2c3493.js" as="script"><link rel="prefetch" href="/assets/GitLu.html-27823973.js" as="script"><link rel="prefetch" href="/assets/JavaKaiFa.html-ff11bb84.js" as="script"><link rel="prefetch" href="/assets/JSR303.html-0c745945.js" as="script"><link rel="prefetch" href="/assets/MinIO.html-958125dd.js" as="script"><link rel="prefetch" href="/assets/ShardingJDBC.html-ce0afe07.js" as="script"><link rel="prefetch" href="/assets/Swagger.html-9c9fb3be.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-4e07419a.js" as="script"><link rel="prefetch" href="/assets/Angular.html-3e716bd5.js" as="script"><link rel="prefetch" href="/assets/ElementUI.html-70835a6c.js" as="script"><link rel="prefetch" href="/assets/Html-Js.html-848bb521.js" as="script"><link rel="prefetch" href="/assets/Vue-basics.html-772005d8.js" as="script"><link rel="prefetch" href="/assets/Vue3_group.html-69369e84.js" as="script"><link rel="prefetch" href="/assets/Vue3_option.html-37ac05d4.js" as="script"><link rel="prefetch" href="/assets/ApifoxAPIceshi.html-0bcb1468.js" as="script"><link rel="prefetch" href="/assets/PDManeryuanshujianmo.html-4c377e02.js" as="script"><link rel="prefetch" href="/assets/OCRwenzishibie.html-b4248616.js" as="script"><link rel="prefetch" href="/assets/EasyExcelbiaogeku.html-511b0f0b.js" as="script"><link rel="prefetch" href="/assets/Javadaimashengchengqi.html-33991c95.js" as="script"><link rel="prefetch" href="/assets/JDKshuangbanben.html-8bba14d4.js" as="script"><link rel="prefetch" href="/assets/JSR303canshuxiaoyan.html-7d12c0ef.js" as="script"><link rel="prefetch" href="/assets/Mail  youjian.html-d520132f.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlusxiangjie.html-94d1940a.js" as="script"><link rel="prefetch" href="/assets/MySQLzhucongfuzhi.html-bf156f04.js" as="script"><link rel="prefetch" href="/assets/Redis-API.html-534cd405.js" as="script"><link rel="prefetch" href="/assets/SpringBootshiwu.html-a83407ed.js" as="script"><link rel="prefetch" href="/assets/SpringBootduxiefenli.html-e50eba32.js" as="script"><link rel="prefetch" href="/assets/Springguolvqihelanjieqi.html-b7cdfd68.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-7c2b5253.js" as="script"><link rel="prefetch" href="/assets/quanjuyichang-JSR303.html-1f70d8b7.js" as="script"><link rel="prefetch" href="/assets/Docker.html-51026828.js" as="script"><link rel="prefetch" href="/assets/Dockeryuanchengfangwenbushu.html-f42be316.js" as="script"><link rel="prefetch" href="/assets/Nginxfanxiangdaili.html-a98dc972.js" as="script"><link rel="prefetch" href="/assets/Apai-SheYingOne.html-b5b7ae7c.js" as="script"><link rel="prefetch" href="/assets/Apai-Year.html-ce260a3f.js" as="script"><link rel="prefetch" href="/assets/My2305.html-8daa1c69.js" as="script"><link rel="prefetch" href="/assets/My2306.html-893e00c1.js" as="script"><link rel="prefetch" href="/assets/My2307.html-e78450ff.js" as="script"><link rel="prefetch" href="/assets/My2308.html-0e38205b.js" as="script"><link rel="prefetch" href="/assets/My2309.html-06a48ebc.js" as="script"><link rel="prefetch" href="/assets/My2310.html-59afa2c3.js" as="script"><link rel="prefetch" href="/assets/My2311.html-b634eab1.js" as="script"><link rel="prefetch" href="/assets/My2312.html-aafaa08a.js" as="script"><link rel="prefetch" href="/assets/Yi-HuBei.html-61c8ef5d.js" as="script"><link rel="prefetch" href="/assets/Yi-ZhuHai.html-26a46f60.js" as="script"><link rel="prefetch" href="/assets/Apache ECharts tubiao.html-e9cc0012.js" as="script"><link rel="prefetch" href="/assets/Vue3 zuheshi API.html-11ae0fbc.js" as="script"><link rel="prefetch" href="/assets/Vue3 xuanxiangshi API.html-ce744458.js" as="script"><link rel="prefetch" href="/assets/Vue3-i18n-guojihuachajian.html-1fa338e8.js" as="script"><link rel="prefetch" href="/assets/Batpichulijiaoben.html-06ab27bc.js" as="script"><link rel="prefetch" href="/assets/Gitgongzuoliu.html-b1e45d5c.js" as="script"><link rel="prefetch" href="/assets/Typoratuchuang.html-cb5c9b25.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-c36453a2.js" as="script"><link rel="prefetch" href="/assets/My2401.html-9b16cb88.js" as="script"><link rel="prefetch" href="/assets/My2402.html-3ade37a1.js" as="script"><link rel="prefetch" href="/assets/shenhai-0509.html-648d7d95.js" as="script"><link rel="prefetch" href="/assets/zhuhaixing.html-7d486783.js" as="script"><link rel="prefetch" href="/assets/404.html-7c90b0cc.js" as="script"><link rel="prefetch" href="/assets/reco-valine-a0c1af1f.js" as="script">
    <link rel="preload" href="/assets/style-8a0f09c6.css" as="style"><link rel="stylesheet" href="/assets/style-8a0f09c6.css">
  </head>
  <body>
    <div id="app"><!--[--><div class="theme-container"><div class="common-wrapper show-series show-catalog"><div><header class="navbar-container"><!--[--><div class="site-brand nav-item"><img class="logo" src="/logo.png" alt="阿派 | Apai Blog"><a href="/" class="site-name can-hide">阿派 | Apai Blog</a></div><div class="nav-item navbar-links-wrapper" style=""><form class="search-box" role="search"><input type="search" autocomplete="off" spellcheck="false" value><!----></form><nav class="navbar-links"><!--[--><div class="navbar-links__item"><a href="/" class="link router-link-active" aria-label="Home"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Home<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/categories/Java/1/" class="link" aria-label="分类组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->分类组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/tags/Rests/1/" class="link" aria-label="标签组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->标签组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="文档组"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="文档组"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Web-develop/Html-Js" class="link" aria-label="Web-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Web-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Java-develop/JavaMyUtil" class="link" aria-label="Java-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Linux-develop/Linux" class="link" aria-label="Linux-系统"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Linux-系统<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Rests-docs/Vuepress" class="link" aria-label="Apai-其他"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Apai-其他<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="花圃里"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="花圃里"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2023/My2307" class="link" aria-label="2023-花开万里"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2023-花开万里<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2024/My2401" class="link" aria-label="2024-随心而来"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2024-随心而来<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><a href="/docs/Apai-AboutMe/Lu-aboutMe" class="link" aria-label="关于我"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->关于我<!--]--></span></span><!--[--><!--]--></a></div><!--]--></nav><span class="xicon-container btn-toggle-dark-mode btn--dark-mode"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" style="width:20px;height:20px;font-size:20px;color:inherit;"><path d="M15 2h2v3h-2z" fill="currentColor"></path><path d="M27 15h3v2h-3z" fill="currentColor"></path><path d="M15 27h2v3h-2z" fill="currentColor"></path><path d="M2 15h3v2H2z" fill="currentColor"></path><path d="M5.45 6.884l1.414-1.415l2.121 2.122l-1.414 1.414z" fill="currentColor"></path><path d="M23 7.58l2.121-2.12l1.414 1.414l-2.121 2.121z" fill="currentColor"></path><path d="M23.002 24.416l1.415-1.414l2.12 2.122l-1.413 1.414z" fill="currentColor"></path><path d="M5.47 25.13L7.59 23L9 24.42l-2.12 2.12l-1.41-1.41z" fill="currentColor"></path><path d="M16 8a8 8 0 1 0 8 8a8 8 0 0 0-8-8zm0 14a6 6 0 0 1 0-12z" fill="currentColor"></path></svg></span><span class="xicon-container btn-toggle-menus"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" style="width:20px;height:20px;font-size:20px;color:inherit;"><circle cx="16" cy="8" r="2" fill="currentColor"></circle><circle cx="16" cy="16" r="2" fill="currentColor"></circle><circle cx="16" cy="24" r="2" fill="currentColor"></circle></svg></span></div><!--]--></header><div class="mobile-menus-container"><nav class="navbar-links mobile"><!--[--><div class="navbar-links__item"><a href="/" class="link router-link-active" aria-label="Home"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Home<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/categories/Java/1/" class="link" aria-label="分类组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->分类组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/tags/Rests/1/" class="link" aria-label="标签组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->标签组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="文档组"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="文档组"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Web-develop/Html-Js" class="link" aria-label="Web-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Web-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Java-develop/JavaMyUtil" class="link" aria-label="Java-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Linux-develop/Linux" class="link" aria-label="Linux-系统"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Linux-系统<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Rests-docs/Vuepress" class="link" aria-label="Apai-其他"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Apai-其他<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="花圃里"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="花圃里"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2023/My2307" class="link" aria-label="2023-花开万里"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2023-花开万里<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2024/My2401" class="link" aria-label="2024-随心而来"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2024-随心而来<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><a href="/docs/Apai-AboutMe/Lu-aboutMe" class="link" aria-label="关于我"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->关于我<!--]--></span></span><!--[--><!--]--></a></div><!--]--></nav><div class="appearance"><span>Appearance</span><span class="xicon-container btn-toggle-dark-mode"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" style="width:20px;height:20px;font-size:20px;color:inherit;"><path d="M15 2h2v3h-2z" fill="currentColor"></path><path d="M27 15h3v2h-3z" fill="currentColor"></path><path d="M15 27h2v3h-2z" fill="currentColor"></path><path d="M2 15h3v2H2z" fill="currentColor"></path><path d="M5.45 6.884l1.414-1.415l2.121 2.122l-1.414 1.414z" fill="currentColor"></path><path d="M23 7.58l2.121-2.12l1.414 1.414l-2.121 2.121z" fill="currentColor"></path><path d="M23.002 24.416l1.415-1.414l2.12 2.122l-1.413 1.414z" fill="currentColor"></path><path d="M5.47 25.13L7.59 23L9 24.42l-2.12 2.12l-1.41-1.41z" fill="currentColor"></path><path d="M16 8a8 8 0 1 0 8 8a8 8 0 0 0-8-8zm0 14a6 6 0 0 1 0-12z" fill="currentColor"></path></svg></span></div></div><div class="series-mask"></div><aside class="series-container"><div class="site-brand"><img class="logo" src="/logo.png" alt="阿派 | Apai Blog"><a href="/" class="site-name can-hide">阿派 | Apai Blog</a></div><!--[--><!--[--><section class="series-group series-item"><h5 class="series-heading">Java 笔记</h5><ul><li><!--[--><a href="/docs/Java-develop/JavaMyUtil.html" class="link series-item" aria-label="阿派的工具类"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->阿派的工具类<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/Java-basics.html" class="link series-item" aria-label="Java 基础"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java 基础<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/JavaJinJie02.html" class="link series-item" aria-label="Java 基础_进阶二"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java 基础_进阶二<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/JavaGongNeng03.html" class="link series-item" aria-label="Java 功能实现"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java 功能实现<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/JavaBiKeng04.html" class="link series-item" aria-label="Java 避坑及技巧"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java 避坑及技巧<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--[--><section class="series-group series-item"><h5 class="series-heading">Java 基础</h5><ul><li><!--[--><a href="/docs/Java-develop/JavaAPI.html" class="link series-item" aria-label="Java API"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java API<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/WebSocket.html" class="link series-item" aria-label="WebSocket"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->WebSocket<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/ShiWuAffair.html" class="link series-item" aria-label="Spring Boot 事务"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Spring Boot 事务<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--[--><section class="series-group series-item"><h5 class="series-heading active">MySql 数据库</h5><ul><li><!--[--><a href="/docs/Java-develop/MySql.html" class="link series-item" aria-label="MySql 数据库"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 数据库<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html" class="router-link-active router-link-exact-active link router-link-active series-item active" aria-label="MySql 函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 函数<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--[--><section class="series-group series-item"><h5 class="series-heading">Java 框架</h5><ul><li><!--[--><a href="/docs/Java-develop/Spring.html" class="link series-item" aria-label="Spring_框架"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Spring_框架<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/SpringPeiZhi.html" class="link series-item" aria-label="Spring 配置详解"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Spring 配置详解<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/MyBatisPlus.html" class="link series-item" aria-label="MyBatisPlus_框架"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MyBatisPlus_框架<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/SpringSecurity.html" class="link series-item" aria-label="SpringSecurity 安全框架"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->SpringSecurity 安全框架<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--[--><section class="series-group series-item"><h5 class="series-heading">微服务</h5><ul><li><!--[--><a href="/docs/Java-develop/SpringConfig.html" class="link series-item" aria-label="SpringConfig 微服务"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->SpringConfig 微服务<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/Redis.html" class="link series-item" aria-label="Redis 内存数据库"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Redis 内存数据库<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/RabbitMQ.html" class="link series-item" aria-label="RabbitMQ 消息队列"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->RabbitMQ 消息队列<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--]--></aside><!--[--><main class="page-container"><h1 class="page-title">MySql 函数</h1><div class="page-info"><span class="xicon-container left"><!--[--><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" class="xicon-icon" style="width:18px;height:18px;font-size:18px;color:inherit;"><path d="M16 4a5 5 0 1 1-5 5a5 5 0 0 1 5-5m0-2a7 7 0 1 0 7 7a7 7 0 0 0-7-7z" fill="currentColor"></path><path d="M26 30h-2v-5a5 5 0 0 0-5-5h-6a5 5 0 0 0-5 5v5H6v-5a7 7 0 0 1 7-7h6a7 7 0 0 1 7 7z" fill="currentColor"></path></svg><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->LuisApai<!--]--></span></span><span class="xicon-container left"><!--[--><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" class="xicon-icon" style="width:18px;height:18px;font-size:18px;color:inherit;"><path d="M26 4h-4V2h-2v2h-8V2h-2v2H6c-1.1 0-2 .9-2 2v20c0 1.1.9 2 2 2h20c1.1 0 2-.9 2-2V6c0-1.1-.9-2-2-2zm0 22H6V12h20v14zm0-16H6V6h4v2h2V6h8v2h2V6h4v4z" fill="currentColor"></path></svg><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2023/06/19<!--]--></span></span><!----><!----><!----></div><div class="theme-reco-default-content"><div><h2 id="mysql-基础规范" tabindex="-1"><a class="header-anchor" href="#mysql-基础规范" aria-hidden="true">#</a> MySql 基础规范</h2><h3 id="表-基础规范" tabindex="-1"><a class="header-anchor" href="#表-基础规范" aria-hidden="true">#</a> 表 基础规范</h3><blockquote><p>表 | 必备字段:</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span> <span class="token keyword">bigint</span><span class="token punctuation">(</span><span class="token number">18</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">COMMENT</span> <span class="token string">&#39;主键id&#39;</span><span class="token punctuation">,</span>

<span class="token identifier"><span class="token punctuation">`</span>creator_id<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">&#39;&#39;</span> 表必须包<span class="token keyword">COMMENT</span> <span class="token string">&#39;创建人id&#39;</span><span class="token punctuation">,</span>
<span class="token identifier"><span class="token punctuation">`</span>modifier_id<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">&#39;&#39;</span> <span class="token keyword">COMMENT</span> <span class="token string">&#39;修改人id&#39;</span><span class="token punctuation">,</span>
<span class="token identifier"><span class="token punctuation">`</span>create_time<span class="token punctuation">`</span></span> <span class="token keyword">datetime</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">COMMENT</span> <span class="token string">&#39;创建时间&#39;</span><span class="token punctuation">,</span>
<span class="token identifier"><span class="token punctuation">`</span>modify_time<span class="token punctuation">`</span></span> <span class="token keyword">datetime</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">COMMENT</span> <span class="token string">&#39;更新时间&#39;</span><span class="token punctuation">,</span>
<span class="token identifier"><span class="token punctuation">`</span>is_delete<span class="token punctuation">`</span></span> <span class="token keyword">tinyint</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">&#39;0&#39;</span> <span class="token keyword">COMMENT</span> <span class="token string">&#39;删除标识：0未删除1已删除&#39;</span><span class="token punctuation">,</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>说明：</p></blockquote><ul><li>主键id：独立的自增的bigint，与业务无关</li><li>创建人id：记录创建人</li><li>修改人id：记录修改人</li><li>创建时间：程序维护、不修改，建索引</li><li>更新时间：数据维护</li><li>删除标识：软删除</li></ul><blockquote><p>注意事项:</p></blockquote><p><strong>1.主键使用自增 int/bigint</strong></p><p>主键id：独立的自增的bigint，与业务无关，禁止修改</p><ul><li>避免插入数据造成数据分页</li><li>提高二级索引的性能，二级索引包含主键</li><li>大表一定要bigint，防止int不够用（大约21亿左右），溢出后会导致报错。</li></ul><p><strong>2.禁止使用 default NULL</strong></p><p>所有字段均定义为NOT NULL：</p><ul><li>字符为default ‘’</li><li>数值为default 0</li><li>时间为default 0000-00-00 00:00:00</li></ul><h3 id="sql-规范" tabindex="-1"><a class="header-anchor" href="#sql-规范" aria-hidden="true">#</a> SQL 规范</h3><ol><li><p>访问量很大的SQL不要在数据库里做排序</p></li><li><p>禁止在DB里排序，请在web server上排序（对并发量特别大的SQL），web server有上百台，而db仅仅只有个位数的数量，排序都在db，会把db压垮。</p></li><li><p>禁止在where子句中对字段施加函数，如to_date(add_time)&gt;xxxxx, 应改为: add_time &gt;= unix_timestamp(date_add(str_to_date(&#39;20220227&#39;,&#39;%Y%m%d&#39;),interval - 29 day))</p></li><li><p>禁止使用%做前缀模糊查询，例如LIKE “%weibo”，无法使用索引。</p></li><li><p>禁止使用select *，兼容性差，使用更多的网络流量，不能使用覆盖索引，必须回表</p></li><li><p>禁止3个以上的表join，join的字段数据类型必须绝对一致</p></li><li><p>in列表元素不能超过1000个</p></li></ol><h2 id="java-符号的转译" tabindex="-1"><a class="header-anchor" href="#java-符号的转译" aria-hidden="true">#</a> Java 符号的转译</h2><h3 id="模糊查询" tabindex="-1"><a class="header-anchor" href="#模糊查询" aria-hidden="true">#</a> 模糊查询</h3><div class="language-xml line-numbers-mode" data-ext="xml"><pre class="language-xml"><code>    <span class="token comment">&lt;!-- 	SQL - 动态多条件 模糊查询 --&gt;</span>
    方式一: concat()  ,#{ename}
	and 列名 like concat(&#39;%&#39;,#{字段},&#39;%&#39;)
    方式二: sql映射文件中使用 ${} 取值表达式
	and 列名 like &#39;%${字段}%&#39;
    方式三： 使用bind标签  [了解]
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>bind</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>别名<span class="token punctuation">&quot;</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span><span class="token punctuation">&#39;</span>%&#39;+字段+&#39;%&#39;<span class="token punctuation">&quot;</span></span><span class="token punctuation">/&gt;</span></span>
	and 列名 like #{别名}
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="范围查询" tabindex="-1"><a class="header-anchor" href="#范围查询" aria-hidden="true">#</a> 范围查询</h3><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token operator">&lt;</span><span class="token operator">!</span><span class="token operator">--</span> 	<span class="token constant">SQL</span> <span class="token operator">-</span> 动态多条件 范围区间查询 <span class="token operator">--</span><span class="token operator">&gt;</span>
 <span class="token comment">// 正常sql查询列值的范围</span>
select 列名<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> from 表名 where 列名 between 最小值 and 最大值<span class="token punctuation">;</span>
select 列名<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> from 表名 where 列名 <span class="token operator">&gt;=</span> 最小值 and 列名 <span class="token operator">&lt;=</span> 最大值<span class="token punctuation">;</span>
 <span class="token comment">// 方式一: 使用 大于 大于 注意: 小于有关的会报错 可以让字段和列名对调位置使用大于</span>
 	and 列名 <span class="token operator">&lt;</span> #<span class="token punctuation">{</span>字段<span class="token punctuation">}</span>
 <span class="token comment">// 方式二: 使用 &lt;![CDATA[ 语句 ]]&gt;</span>
 	<span class="token operator">&lt;</span><span class="token operator">!</span><span class="token punctuation">[</span><span class="token constant">CDATA</span><span class="token punctuation">[</span> and nums <span class="token operator">&lt;=</span> #<span class="token punctuation">{</span>maxnums<span class="token punctuation">}</span> <span class="token punctuation">]</span><span class="token punctuation">]</span><span class="token operator">&gt;</span>
 <span class="token comment">// 方式三： 使用转义</span>
 	大于号	<span class="token operator">&gt;</span>	<span class="token operator">&amp;</span>gt<span class="token punctuation">;</span>
	小于号	<span class="token operator">&lt;</span>	<span class="token operator">&amp;</span>lt<span class="token punctuation">;</span>
	大于等于 <span class="token operator">&gt;=</span>	<span class="token operator">&amp;</span>gt<span class="token punctuation">;</span><span class="token operator">=</span>
	小于等于 <span class="token operator">&lt;=</span>	<span class="token operator">&amp;</span>lt<span class="token punctuation">;</span><span class="token operator">=</span>
	与符号	<span class="token operator">&amp;</span>	<span class="token operator">&amp;</span>amp<span class="token punctuation">;</span>
	单引号	&#39;	<span class="token operator">&amp;</span>apos<span class="token punctuation">;</span>
	双引号	&quot;	<span class="token operator">&amp;</span>quot<span class="token punctuation">;</span>
         
<span class="token comment">// 时间类型对比 | mysql默认时间对比时转换未 字符串 则时间的条件也必须是字符串</span>
时间对比<span class="token operator">:</span> 直接使用 对比符 如<span class="token operator">:</span> <span class="token operator">&amp;</span>gt<span class="token punctuation">;</span> <span class="token operator">&amp;</span>lt<span class="token punctuation">;</span> 等
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="时间类函数" tabindex="-1"><a class="header-anchor" href="#时间类函数" aria-hidden="true">#</a> 时间类函数</h2><h3 id="时间范围条件" tabindex="-1"><a class="header-anchor" href="#时间范围条件" aria-hidden="true">#</a> 时间范围条件</h3><ul><li>between</li></ul><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 表示: 格式化列busi_date为年月日 根据 开始时间 到 结束时间条件的范围进行查询</span>
<span class="token operator">and</span> date_format<span class="token punctuation">(</span>ssd<span class="token punctuation">.</span>busi_date<span class="token punctuation">,</span> <span class="token string">&#39;%Y-%m-%d&#39;</span><span class="token punctuation">)</span> <span class="token operator">between</span> <span class="token comment">#{entity.startBusiDate} and #{entity.endBusiDate}</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><ul><li>大于 小于</li></ul><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 大于等于</span>
<span class="token operator">and</span> a<span class="token punctuation">.</span>busi_date <span class="token operator">&amp;</span>gt<span class="token punctuation">;</span><span class="token operator">=</span> <span class="token comment">#lentity.startBusiDate]</span>
<span class="token comment">-- 小于等于</span>
<span class="token operator">and</span> a<span class="token punctuation">.</span>busi_date <span class="token operator">&amp;</span>lt<span class="token punctuation">;</span><span class="token operator">=</span> <span class="token comment">#lentity.endBusiDate]</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="获取现在时间函数" tabindex="-1"><a class="header-anchor" href="#获取现在时间函数" aria-hidden="true">#</a> 获取现在时间函数</h3><p>详见: https://www.runoob.com/sql/sql-dates.html</p><table><thead><tr><th><a href="https://www.runoob.com/sql/func-now.html" target="_blank" rel="noopener noreferrer">NOW()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></th><th>返回当前的日期和时间</th></tr></thead><tbody><tr><td><a href="https://www.runoob.com/sql/func-curdate.html" target="_blank" rel="noopener noreferrer">CURDATE()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>返回当前的日期</td></tr><tr><td><a href="https://www.runoob.com/sql/func-curtime.html" target="_blank" rel="noopener noreferrer">CURTIME()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>返回当前的时间</td></tr><tr><td><a href="https://www.runoob.com/sql/func-date.html" target="_blank" rel="noopener noreferrer">DATE()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>提取日期或日期/时间表达式的日期部分</td></tr><tr><td><a href="https://www.runoob.com/sql/func-extract.html" target="_blank" rel="noopener noreferrer">EXTRACT()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>返回日期/时间的单独部分</td></tr><tr><td><a href="https://www.runoob.com/sql/func-date-add.html" target="_blank" rel="noopener noreferrer">DATE_ADD()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>向日期添加指定的时间间隔</td></tr><tr><td><a href="https://www.runoob.com/sql/func-date-sub.html" target="_blank" rel="noopener noreferrer">DATE_SUB()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>从日期减去指定的时间间隔</td></tr><tr><td><a href="https://www.runoob.com/sql/func-datediff-mysql.html" target="_blank" rel="noopener noreferrer">DATEDIFF()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>返回两个日期之间的天数</td></tr><tr><td><a href="https://www.runoob.com/sql/func-date-format.html" target="_blank" rel="noopener noreferrer">DATE_FORMAT()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>用不同的格式显示日期/时间</td></tr></tbody></table><h3 id="时间差列" tabindex="-1"><a class="header-anchor" href="#时间差列" aria-hidden="true">#</a> 时间差列</h3><blockquote><p>可使用 timediff | 时间差 函数计算出两个时间的时间差为新的一列数据</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> timediff<span class="token punctuation">(</span><span class="token string">&quot;时间1&quot;</span> <span class="token punctuation">,</span> <span class="token string">&quot;时间2&quot;</span><span class="token punctuation">)</span> <span class="token keyword">from</span> 表名
<span class="token keyword">select</span> timediff<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">,</span> seckill_endtime<span class="token punctuation">)</span> <span class="token keyword">from</span> seckill

<span class="token operator">-</span><span class="token number">63</span>:<span class="token number">13</span>:<span class="token number">46</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="时间差条件" tabindex="-1"><a class="header-anchor" href="#时间差条件" aria-hidden="true">#</a> 时间差条件</h3><p><a href="https://blog.csdn.net/homelam/article/details/88647099" target="_blank" rel="noopener noreferrer">Mysql日期差函数，Mysql选择两个日期字段相差大于或小于一定时间_mysql 时间间隔大于<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></p><blockquote><p>FROM_UNIXTIME | 语法</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> tpaylog <span class="token keyword">where</span> TIMESTAMPDIFF<span class="token punctuation">(</span>时间差类型 <span class="token punctuation">,</span>时间<span class="token number">2</span><span class="token punctuation">,</span> 时间<span class="token number">1</span> <span class="token operator">&gt;</span> <span class="token number">30</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><blockquote><p>时间差类型</p></blockquote><ul><li><p>FRAC_SECOND - 表示间隔是毫秒</p></li><li><p>SECOND - 秒</p></li><li><p>MINUTE - 分钟</p></li><li><p>HOUR - 小时</p></li><li><p>DAY - 天</p></li><li><p>WEEK - 星期</p></li><li><p>MONTH - 月</p></li><li><p>QUARTER - 季度</p></li><li><p>YEAR - 年</p></li></ul><blockquote><p>案例</p><p>https://blog.csdn.net/m0_67394002/article/details/126113579</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 现在的时间 至 该字段的时间 差值 大于30分钟</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> tpaylog <span class="token keyword">where</span> TIMESTAMPDIFF<span class="token punctuation">(</span><span class="token keyword">MINUTE</span> <span class="token punctuation">,</span>tpaylog_ordertime<span class="token punctuation">,</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">&gt;</span> <span class="token number">30</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> tpaylog <span class="token keyword">where</span> TIMESTAMPDIFF<span class="token punctuation">(</span>时间差类型 <span class="token punctuation">,</span>时间字段<span class="token number">1</span><span class="token punctuation">,</span> 时间字段<span class="token number">2</span><span class="token punctuation">)</span> <span class="token operator">&gt;</span> 值

<span class="token comment">-- 类似函数 时间之差大于31天 | datediff(now(),时间字段) &gt;= 31</span>
datediff<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span>busi_date<span class="token punctuation">)</span> <span class="token operator">&gt;=</span> <span class="token number">31</span>

<span class="token comment">-- 类似函数 时间之差小于于7天 | 时间字段 &gt; DATE_SUB(now(),INTERVAL 7 时间单位);</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>stock_info<span class="token punctuation">`</span></span> <span class="token keyword">where</span>
busi_date <span class="token operator">&gt;</span> DATE_SUB<span class="token punctuation">(</span><span class="token string">&#39;2022-11-11&#39;</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">7</span> <span class="token keyword">day</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="时间转换格式" tabindex="-1"><a class="header-anchor" href="#时间转换格式" aria-hidden="true">#</a> 时间转换格式</h3><p>详见: https://www.runoob.com/sql/func-date-format.html</p><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token comment">// date_format(时间字段, 时间格式) = 条件值</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token comment">// busi_date 时间字段 | 如: 2022-11-04 --&gt; 2022-11</span>
select <span class="token operator">*</span> from stock_in_order where
    audit_status <span class="token operator">&gt;</span> <span class="token number">2</span> and category <span class="token operator">=</span> <span class="token number">2</span> and company_id <span class="token operator">=</span> <span class="token number">37</span> and status <span class="token operator">=</span> <span class="token number">1</span>
    and <span class="token function">date_format</span><span class="token punctuation">(</span>busi_date<span class="token punctuation">,</span> <span class="token char">&#39;%Y-%m&#39;</span><span class="token punctuation">)</span> <span class="token operator">=</span> &#39;<span class="token number">2022</span><span class="token operator">-</span><span class="token number">10</span>&#39;

<span class="token operator">&lt;</span><span class="token keyword">if</span> test<span class="token operator">=</span><span class="token string">&quot;entity.start != null and entity.start != &#39;&#39;&quot;</span><span class="token operator">&gt;</span>
    and <span class="token function">date_format</span><span class="token punctuation">(</span>a<span class="token punctuation">.</span>create_time<span class="token punctuation">,</span>&#39;<span class="token operator">%</span><span class="token class-name">Y</span><span class="token operator">-</span><span class="token operator">%</span>m<span class="token operator">-</span><span class="token operator">%</span>d&#39;<span class="token punctuation">)</span> <span class="token operator">&amp;</span>gt<span class="token punctuation">;</span><span class="token operator">=</span> #<span class="token punctuation">{</span>entity<span class="token punctuation">.</span>start<span class="token punctuation">}</span>
<span class="token operator">&lt;</span><span class="token operator">/</span><span class="token keyword">if</span><span class="token operator">&gt;</span>
 <span class="token operator">&lt;</span><span class="token keyword">if</span> test<span class="token operator">=</span><span class="token string">&quot;entity.end != null and entity.end != &#39;&#39;&quot;</span><span class="token operator">&gt;</span>
    and <span class="token function">date_format</span><span class="token punctuation">(</span>a<span class="token punctuation">.</span>create_time<span class="token punctuation">,</span>&#39;<span class="token operator">%</span><span class="token class-name">Y</span><span class="token operator">-</span><span class="token operator">%</span>m<span class="token operator">-</span><span class="token operator">%</span>d&#39;<span class="token punctuation">)</span> <span class="token operator">&amp;</span>lt<span class="token punctuation">;</span><span class="token operator">=</span> #<span class="token punctuation">{</span>entity<span class="token punctuation">.</span>end<span class="token punctuation">}</span>
<span class="token operator">&lt;</span><span class="token operator">/</span><span class="token keyword">if</span><span class="token operator">&gt;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="查询时间偏移数据" tabindex="-1"><a class="header-anchor" href="#查询时间偏移数据" aria-hidden="true">#</a> 查询时间偏移数据</h3><p>详见: https://blog.csdn.net/weixin_49071539/article/details/116458017</p><p>​ https://www.cnblogs.com/wjw1014/p/12917335.html</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 今天</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> to_days<span class="token punctuation">(</span>时间字段名<span class="token punctuation">)</span> <span class="token operator">=</span> to_days<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 昨天</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">WHERE</span> TO_DAYS<span class="token punctuation">(</span> <span class="token function">NOW</span><span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token operator">-</span> TO_DAYS<span class="token punctuation">(</span> 时间字段名<span class="token punctuation">)</span> <span class="token operator">&lt;=</span> <span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment">-- 7天</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">where</span> DATE_SUB<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">7</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span> <span class="token operator">&lt;=</span> <span class="token keyword">date</span><span class="token punctuation">(</span>时间字段名<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 近30天</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">where</span> DATE_SUB<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">30</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span> <span class="token operator">&lt;=</span> <span class="token keyword">date</span><span class="token punctuation">(</span>时间字段名<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 本月</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">WHERE</span> DATE_FORMAT<span class="token punctuation">(</span> 时间字段名<span class="token punctuation">,</span> <span class="token string">&#39;%Y%m&#39;</span> <span class="token punctuation">)</span> <span class="token operator">=</span> DATE_FORMAT<span class="token punctuation">(</span> CURDATE<span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">,</span> <span class="token string">&#39;%Y%m&#39;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 上一月 | 使用now()可用 字符串时间字段&#39;2022-10-1&#39;可用</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">WHERE</span> PERIOD_DIFF<span class="token punctuation">(</span> date_format<span class="token punctuation">(</span> <span class="token function">now</span><span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">,</span> <span class="token string">&#39;%Y%m&#39;</span> <span class="token punctuation">)</span> <span class="token punctuation">,</span> date_format<span class="token punctuation">(</span> 时间字段名<span class="token punctuation">,</span> <span class="token string">&#39;%Y%m&#39;</span> <span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询本季度数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token identifier"><span class="token punctuation">`</span>ht_invoice_information<span class="token punctuation">`</span></span> <span class="token keyword">where</span> QUARTER<span class="token punctuation">(</span>create_date<span class="token punctuation">)</span><span class="token operator">=</span>QUARTER<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询上季度数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token identifier"><span class="token punctuation">`</span>ht_invoice_information<span class="token punctuation">`</span></span> <span class="token keyword">where</span> QUARTER<span class="token punctuation">(</span>create_date<span class="token punctuation">)</span><span class="token operator">=</span>QUARTER<span class="token punctuation">(</span>DATE_SUB<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">interval</span> <span class="token number">1</span> QUARTER<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询本年数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token identifier"><span class="token punctuation">`</span>ht_invoice_information<span class="token punctuation">`</span></span> <span class="token keyword">where</span> <span class="token keyword">YEAR</span><span class="token punctuation">(</span>create_date<span class="token punctuation">)</span><span class="token operator">=</span><span class="token keyword">YEAR</span><span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询上年数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token identifier"><span class="token punctuation">`</span>ht_invoice_information<span class="token punctuation">`</span></span> <span class="token keyword">where</span> <span class="token keyword">year</span><span class="token punctuation">(</span>create_date<span class="token punctuation">)</span><span class="token operator">=</span><span class="token keyword">year</span><span class="token punctuation">(</span>date_sub<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">interval</span> <span class="token number">1</span> <span class="token keyword">year</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询当前这周的数据</span>
<span class="token keyword">SELECT</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">FROM</span> enterprise <span class="token keyword">WHERE</span> YEARWEEK<span class="token punctuation">(</span>date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m-%d&#39;</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> YEARWEEK<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询上周的数据</span>
<span class="token keyword">SELECT</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">FROM</span> enterprise <span class="token keyword">WHERE</span> YEARWEEK<span class="token punctuation">(</span>date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m-%d&#39;</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> YEARWEEK<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询当前月份的数据</span>
<span class="token keyword">select</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">from</span> enterprise   <span class="token keyword">where</span> date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m&#39;</span><span class="token punctuation">)</span><span class="token operator">=</span>date_format<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">&#39;%Y-%m&#39;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询距离当前现在6个月的数据</span>
<span class="token keyword">select</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">from</span> enterprise <span class="token keyword">where</span> submittime <span class="token operator">between</span> date_sub<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">interval</span> <span class="token number">6</span> <span class="token keyword">month</span><span class="token punctuation">)</span> <span class="token operator">and</span> <span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
​<span class="token identifier"><span class="token punctuation">`</span><span class="token punctuation">`</span><span class="token punctuation">`</span>

-- 近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) &lt;= date(时间字段名);

-- 本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, &#39;%Y%m&#39; ) = DATE_FORMAT( CURDATE( ) , &#39;%Y%m&#39; );

-- 上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , &#39;%Y%m&#39; ) , date_format( 时间字段名, &#39;%Y%m&#39; ) ) =1;

-- 查询本季度数据
select * from <span class="token punctuation">`</span></span>ht_invoice_information<span class="token identifier"><span class="token punctuation">`</span> where QUARTER(create_date)=QUARTER(now());

-- 查询上季度数据
select * from <span class="token punctuation">`</span></span>ht_invoice_information<span class="token identifier"><span class="token punctuation">`</span> where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

-- 查询本年数据
select * from <span class="token punctuation">`</span></span>ht_invoice_information<span class="token identifier"><span class="token punctuation">`</span> where YEAR(create_date)=YEAR(NOW());

-- 查询上年数据
select * from <span class="token punctuation">`</span></span>ht_invoice_information<span class="token punctuation">`</span> <span class="token keyword">where</span> <span class="token keyword">year</span><span class="token punctuation">(</span>create_date<span class="token punctuation">)</span><span class="token operator">=</span><span class="token keyword">year</span><span class="token punctuation">(</span>date_sub<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">interval</span> <span class="token number">1</span> <span class="token keyword">year</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询当前这周的数据</span>
<span class="token keyword">SELECT</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">FROM</span> enterprise <span class="token keyword">WHERE</span> YEARWEEK<span class="token punctuation">(</span>date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m-%d&#39;</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> YEARWEEK<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询上周的数据</span>
<span class="token keyword">SELECT</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">FROM</span> enterprise <span class="token keyword">WHERE</span> YEARWEEK<span class="token punctuation">(</span>date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m-%d&#39;</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> YEARWEEK<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询当前月份的数据</span>
<span class="token keyword">select</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">from</span> enterprise   <span class="token keyword">where</span> date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m&#39;</span><span class="token punctuation">)</span><span class="token operator">=</span>date_format<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">&#39;%Y-%m&#39;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询距离当前现在6个月的数据</span>
<span class="token keyword">select</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">from</span> enterprise <span class="token keyword">where</span> submittime <span class="token operator">between</span> date_sub<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">interval</span> <span class="token number">6</span> <span class="token keyword">month</span><span class="token punctuation">)</span> <span class="token operator">and</span> <span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="数学函数" tabindex="-1"><a class="header-anchor" href="#数学函数" aria-hidden="true">#</a> 数学函数</h2><p>https://blog.csdn.net/qq_43842093/article/details/120938688</p><h3 id="绝对值" tabindex="-1"><a class="header-anchor" href="#绝对值" aria-hidden="true">#</a> 绝对值</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- ABS(数值字段) 绝对值</span>
<span class="token keyword">SELECT</span> ABS<span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">33</span><span class="token punctuation">)</span> <span class="token comment">-- 输出: 字段是绝对值 没有负数 33</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="余数" tabindex="-1"><a class="header-anchor" href="#余数" aria-hidden="true">#</a> 余数</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- MOD(N,M) 或%:返回N被M除的余数</span>
<span class="token keyword">SELECT</span> <span class="token function">MOD</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token comment">-- 输出余数 1</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="不小于最小整数" tabindex="-1"><a class="header-anchor" href="#不小于最小整数" aria-hidden="true">#</a> 不小于最小整数</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- CEILING(X) 返回不小于X的最小整数值。</span>
<span class="token keyword">select</span> CEILING<span class="token punctuation">(</span><span class="token number">1.23</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 输出余数 2</span>
<span class="token keyword">select</span> CEILING<span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">1.23</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 输出余数 -1</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="四舍五入的整数" tabindex="-1"><a class="header-anchor" href="#四舍五入的整数" aria-hidden="true">#</a> 四舍五入的整数</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- ROUND(X) :返回参数X的四舍五入的一个整数。</span>
<span class="token keyword">select</span> <span class="token function">ROUND</span><span class="token punctuation">(</span><span class="token number">1.58</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 输出余数 2</span>
<span class="token keyword">select</span> <span class="token function">ROUND</span><span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">1.58</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 输出余数 -2</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="字符串函数" tabindex="-1"><a class="header-anchor" href="#字符串函数" aria-hidden="true">#</a> 字符串函数</h2><h3 id="ascii-代码值" tabindex="-1"><a class="header-anchor" href="#ascii-代码值" aria-hidden="true">#</a> ASCII 代码值</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- ASCII(str):返回字符串str的最左面字符的ASCII代码值。</span>
<span class="token comment">-- 如果str是空字符串，返回0。如果str是NULL，返回NULL。</span>
<span class="token keyword">select</span> ASCII<span class="token punctuation">(</span><span class="token boolean">null</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- null</span>
<span class="token keyword">select</span> ASCII<span class="token punctuation">(</span><span class="token string">&#39;&#39;</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 0</span>
<span class="token keyword">select</span> ASCII<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 50</span>
<span class="token keyword">select</span> ASCII<span class="token punctuation">(</span><span class="token string">&#39;dx&#39;</span><span class="token punctuation">)</span> <span class="token comment">-- 100</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="合并多列为一列" tabindex="-1"><a class="header-anchor" href="#合并多列为一列" aria-hidden="true">#</a> 合并多列为一列</h3><blockquote><p>CONCAT 语法</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。</span>
<span class="token comment">-- CONCAT(列名1,&quot;/&quot;,列名2,&quot;/&quot;,列名3) 别名</span>
CONCAT<span class="token punctuation">(</span>c1<span class="token punctuation">.</span>category_name<span class="token punctuation">,</span><span class="token string">&quot;/&quot;</span><span class="token punctuation">,</span>c2<span class="token punctuation">.</span>category_name<span class="token punctuation">,</span><span class="token string">&quot;/&quot;</span><span class="token punctuation">,</span>c3<span class="token punctuation">.</span>category_name<span class="token punctuation">)</span> cname

<span class="token comment">-- 先分组 在合并多列为一列</span>
<span class="token comment">-- GROUP_CONCAT(列名1,列名2) 别名</span>
GROUP_CONCAT<span class="token punctuation">(</span>id<span class="token punctuation">,</span>username<span class="token punctuation">)</span> cname

<span class="token comment">-- 分组后多行列值合并一行 并进行倒序</span>
<span class="token comment">-- 按 attendance_time 分组, 将每一组的 user_id 列每一行合并一行并倒序</span>
<span class="token keyword">SELECT</span>
	GROUP_CONCAT<span class="token punctuation">(</span> <span class="token string">&quot;&quot;</span><span class="token punctuation">,</span> user_id <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> user_id <span class="token keyword">DESC</span><span class="token punctuation">,</span> <span class="token string">&quot;&quot;</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> day_check_time
<span class="token keyword">FROM</span>
	<span class="token identifier"><span class="token punctuation">`</span>checking_detail<span class="token punctuation">`</span></span>
	<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>
	attendance_time
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>案例</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> s<span class="token punctuation">.</span><span class="token operator">*</span><span class="token punctuation">,</span> CONCAT<span class="token punctuation">(</span>c1<span class="token punctuation">.</span>category_name<span class="token punctuation">,</span><span class="token string">&quot;/&quot;</span><span class="token punctuation">,</span>c2<span class="token punctuation">.</span>category_name<span class="token punctuation">,</span><span class="token string">&quot;/&quot;</span><span class="token punctuation">,</span>c3<span class="token punctuation">.</span>category_name<span class="token punctuation">)</span> cname
<span class="token keyword">from</span> spu s
<span class="token keyword">inner</span> <span class="token keyword">join</span> category c1 <span class="token keyword">on</span> s<span class="token punctuation">.</span>category_id1 <span class="token operator">=</span> c1<span class="token punctuation">.</span>category_id
<span class="token keyword">inner</span> <span class="token keyword">join</span> category c2 <span class="token keyword">on</span> s<span class="token punctuation">.</span>category_id2 <span class="token operator">=</span> c2<span class="token punctuation">.</span>category_id
<span class="token keyword">inner</span> <span class="token keyword">join</span> category c3 <span class="token keyword">on</span> s<span class="token punctuation">.</span>category_id3 <span class="token operator">=</span> c3<span class="token punctuation">.</span>category_id

<span class="token keyword">SELECT</span> GROUP_CONCAT<span class="token punctuation">(</span>id<span class="token punctuation">,</span>username<span class="token punctuation">)</span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>rbac_user<span class="token punctuation">`</span></span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><img src="https://cdn.jsdelivr.net/gh/LuJunandapai/ApaiImage@main/MaYun-PicGo/image-20220809144852195.png" alt="image-20220809144852195"></p><p><img src="https://cdn.jsdelivr.net/gh/LuJunandapai/ApaiImage@main/MaYun-PicGo/image-20220819220210334.png" alt="image-20220819220210334"></p><h3 id="获取字符串长度" tabindex="-1"><a class="header-anchor" href="#获取字符串长度" aria-hidden="true">#</a> 获取字符串长度</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- LENGTH(str):返回字符串str的长度。</span>
<span class="token keyword">select</span> LENGTH<span class="token punctuation">(</span>‘<span class="token keyword">text</span>’<span class="token punctuation">)</span><span class="token punctuation">;</span>  <span class="token comment">-- 4</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="截取字符串" tabindex="-1"><a class="header-anchor" href="#截取字符串" aria-hidden="true">#</a> 截取字符串</h3><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token operator">--</span> <span class="token function">LEFT</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>len<span class="token punctuation">)</span><span class="token operator">:</span>返回字符串str的最左面len个字符。
select <span class="token function">LEFT</span><span class="token punctuation">(</span>‘foobarbar’<span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> fooba

<span class="token operator">--</span> <span class="token function">RIGHT</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>len<span class="token punctuation">)</span><span class="token operator">:</span>返回字符串str的最右面len个字符
select <span class="token function">RIGHT</span><span class="token punctuation">(</span>‘foobarbar’<span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> rbar

<span class="token operator">--</span> <span class="token function">SUBSTRING</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>pos<span class="token punctuation">)</span><span class="token operator">:</span>从字符串str的起始位置pos到最后 返回一个子串。
select <span class="token function">SUBSTRING</span><span class="token punctuation">(</span>‘<span class="token class-name">Quadratically</span>’<span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> ratically

<span class="token operator">--</span> <span class="token function">TRIM</span><span class="token punctuation">(</span>str<span class="token punctuation">)</span><span class="token operator">:</span>返回字符串str，所有前缀或后缀被删除了
select <span class="token function">TRIM</span><span class="token punctuation">(</span>’ bar &#39;<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> bar
            
<span class="token operator">--</span> <span class="token function">LTRIM</span><span class="token punctuation">(</span>str<span class="token punctuation">)</span><span class="token operator">:</span>返回删除了其前置空格字符的字符串str。
select <span class="token function">LTRIM</span><span class="token punctuation">(</span>’ barbar’<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token operator">--</span> <span class="token function">RTRIM</span><span class="token punctuation">(</span>str<span class="token punctuation">)</span><span class="token operator">:</span>返回删除了其拖后空格字符的字符串str。
select <span class="token function">RTRIM</span><span class="token punctuation">(</span>‘barbar ’<span class="token punctuation">)</span><span class="token punctuation">;</span>
            
<span class="token operator">--</span> <span class="token function">REPLACE</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>from_str<span class="token punctuation">,</span>to_str<span class="token punctuation">)</span><span class="token operator">:</span>返回字符串str，其字符串from_str的所有出现由字符串to_str代替
            
<span class="token operator">--</span> <span class="token function">REPEAT</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>count<span class="token punctuation">)</span><span class="token operator">:</span>返回由重复countTimes次的字符串str组成的一个字符串。如果count <span class="token operator">&lt;=</span> <span class="token number">0</span>，返回一个空字符串。如果str或count是<span class="token constant">NULL</span>，返回<span class="token constant">NULL</span>。
select <span class="token function">REPEAT</span><span class="token punctuation">(</span>‘<span class="token class-name">MySQL</span>’<span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> <span class="token class-name">MySQLMySQLMySQL</span>
            
<span class="token operator">--</span> <span class="token function">REVERSE</span><span class="token punctuation">(</span>str<span class="token punctuation">)</span><span class="token operator">:</span>返回颠倒字符顺序的字符串str。
select <span class="token function">REVERSE</span><span class="token punctuation">(</span>‘abc’<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> cba
            
<span class="token operator">--</span> <span class="token function">INSERT</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>pos<span class="token punctuation">,</span>len<span class="token punctuation">,</span>newstr<span class="token punctuation">)</span><span class="token operator">:</span>返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。
select <span class="token function">INSERT</span><span class="token punctuation">(</span>‘whatareyou’<span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> ‘is’<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> whatisyou
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="其他函数" tabindex="-1"><a class="header-anchor" href="#其他函数" aria-hidden="true">#</a> 其他函数</h2><h3 id="判空列" tabindex="-1"><a class="header-anchor" href="#判空列" aria-hidden="true">#</a> 判空列</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span>
<span class="token comment">-- thirtysum 为null 则 值为0 反之还是原来的值 as: 别名</span>
<span class="token keyword">COALESCE</span><span class="token punctuation">(</span>thirtysum<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span> <span class="token keyword">as</span> thirtyDaysAmount<span class="token punctuation">,</span>
<span class="token keyword">from</span> stock_in_order s
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="双select查询成一表" tabindex="-1"><a class="header-anchor" href="#双select查询成一表" aria-hidden="true">#</a> 双select查询成一表</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span>
	u<span class="token punctuation">.</span>name <span class="token keyword">as</span> unitName<span class="token punctuation">,</span> u<span class="token punctuation">.</span>id <span class="token keyword">as</span> unitId<span class="token punctuation">,</span>
	<span class="token function">sum</span><span class="token punctuation">(</span>s<span class="token punctuation">.</span>amount<span class="token punctuation">)</span> <span class="token keyword">as</span> endAmount
<span class="token comment">-- 将查询出来的归于一张表进行左连接 使用对应的字段对接</span>
<span class="token keyword">left</span> <span class="token keyword">join</span> <span class="token punctuation">(</span>
	<span class="token keyword">select</span> 
    	unit_id<span class="token punctuation">,</span> <span class="token function">sum</span><span class="token punctuation">(</span>amount<span class="token punctuation">)</span> thirtysum 
    <span class="token keyword">from</span> stock_in_order 
	<span class="token keyword">where</span> audit_status <span class="token operator">&gt;</span> <span class="token number">2</span> <span class="token operator">and</span> category <span class="token operator">=</span> <span class="token number">2</span> 
          <span class="token operator">and</span> TIMESTAMPDIFF<span class="token punctuation">(</span><span class="token keyword">DAY</span>  <span class="token punctuation">,</span>busi_date<span class="token punctuation">,</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">&amp;</span>lt<span class="token punctuation">;</span> <span class="token number">30</span> <span class="token operator">and</span> unit_id <span class="token operator">is</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">group</span> <span class="token keyword">by</span> unit_id
<span class="token punctuation">)</span> <span class="token keyword">as</span> s1 <span class="token keyword">on</span> s1<span class="token punctuation">.</span>unit_id <span class="token operator">=</span> s<span class="token punctuation">.</span>unit_id
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="联合外全-查询" tabindex="-1"><a class="header-anchor" href="#联合外全-查询" aria-hidden="true">#</a> 联合外全 查询</h3><blockquote><p>Union | all 即 将多张表的数据一次性显示成一张表的数据 且列数一致</p></blockquote><p>防坑指南:</p><ul><li>列数不一致 会报错</li><li>列的数据类型 不一致不会报错 但是会导致类型不明确或者转换字符串</li><li>Union 会将行数相同的数据合并一行 去重</li><li>Union all 则不会去重 显示所有数据</li></ul><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- Union 会将行数相同的数据合并一行 去重</span>
<span class="token keyword">select</span> id<span class="token punctuation">,</span> name <span class="token keyword">from</span> a
<span class="token keyword">Union</span> 
<span class="token keyword">select</span> id<span class="token punctuation">,</span> name <span class="token keyword">from</span> b
<span class="token comment">-- Union all 则不会去重 显示所有数据</span>
<span class="token keyword">select</span> id<span class="token punctuation">,</span> name <span class="token keyword">from</span> a
<span class="token keyword">Union</span> <span class="token keyword">all</span>
<span class="token keyword">select</span> id<span class="token punctuation">,</span> name <span class="token keyword">from</span> b
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="条件判断后聚合列" tabindex="-1"><a class="header-anchor" href="#条件判断后聚合列" aria-hidden="true">#</a> 条件判断后聚合列</h3><ul><li><p>以同列值为分组合为一条时 会导致后面对应多条语句</p></li><li><p>使用 条件判断后聚合 创建新的列 值为; 经过 if 判断的聚合函数 的唯一值</p></li></ul><p><strong>语法</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 语法一: </span>
<span class="token comment">-- 聚合函数(if(条件列名=&#39;值&#39;, 为true时的值, 为false时的值)) 列名的别名,</span>
<span class="token function">sum</span><span class="token punctuation">(</span><span class="token keyword">if</span><span class="token punctuation">(</span>b<span class="token punctuation">.</span>sub_name<span class="token operator">=</span><span class="token string">&#39;语文&#39;</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 语文<span class="token punctuation">,</span>

<span class="token comment">-- 语法二:</span>
<span class="token comment">-- 聚合函数(case when 条件列名=&#39;值&#39; then 为true时的值 else 为false时的值 end) 列名的别名,</span>
<span class="token function">sum</span><span class="token punctuation">(</span><span class="token keyword">case</span> <span class="token keyword">when</span> b<span class="token punctuation">.</span>sub_name<span class="token operator">=</span><span class="token string">&#39;语文&#39;</span> <span class="token keyword">then</span> <span class="token number">1</span> <span class="token keyword">else</span> <span class="token number">0</span> <span class="token keyword">end</span><span class="token punctuation">)</span> 语文<span class="token punctuation">,</span>

<span class="token comment">-- 如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。</span>
IFNULL<span class="token punctuation">(</span>expression_1<span class="token punctuation">,</span>expression_2<span class="token punctuation">)</span><span class="token punctuation">;</span>

</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>案例</strong></p><blockquote><p>解释: 如数学 --&gt; 件为真(数学) 则值为数学分数 在判断为假(语文-外语) 值为 0 最后将使用的值进行聚合为一个数值 然后再取列名</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> any_value<span class="token punctuation">(</span>t<span class="token punctuation">.</span>stu_id<span class="token punctuation">)</span> id<span class="token punctuation">,</span>t<span class="token punctuation">.</span>stu_name <span class="token keyword">as</span> <span class="token string">&#39;姓名&#39;</span><span class="token punctuation">,</span> any_value<span class="token punctuation">(</span>g<span class="token punctuation">.</span>gra_name<span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token string">&#39;班级&#39;</span><span class="token punctuation">,</span>
<span class="token function">sum</span><span class="token punctuation">(</span><span class="token keyword">if</span><span class="token punctuation">(</span>b<span class="token punctuation">.</span>sub_name<span class="token operator">=</span><span class="token string">&#39;语文&#39;</span><span class="token punctuation">,</span> s<span class="token punctuation">.</span>sco_grade<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 语文<span class="token punctuation">,</span>
<span class="token function">sum</span><span class="token punctuation">(</span><span class="token keyword">case</span> <span class="token keyword">when</span> b<span class="token punctuation">.</span>sub_name<span class="token operator">=</span><span class="token string">&#39;数学&#39;</span> <span class="token keyword">then</span> s<span class="token punctuation">.</span>sco_grade <span class="token keyword">else</span> <span class="token number">0</span> <span class="token keyword">end</span><span class="token punctuation">)</span> 数学<span class="token punctuation">,</span>
<span class="token function">sum</span><span class="token punctuation">(</span><span class="token keyword">case</span> <span class="token keyword">when</span> b<span class="token punctuation">.</span>sub_name<span class="token operator">=</span><span class="token string">&#39;外语&#39;</span> <span class="token keyword">then</span> s<span class="token punctuation">.</span>sco_grade <span class="token keyword">else</span> <span class="token number">0</span> <span class="token keyword">end</span><span class="token punctuation">)</span> 外语
<span class="token keyword">FROM</span> stu_student t
<span class="token keyword">left</span> <span class="token keyword">JOIN</span> stu_score s <span class="token keyword">on</span> s<span class="token punctuation">.</span>sco_stuid <span class="token operator">=</span> t<span class="token punctuation">.</span>stu_id 
<span class="token keyword">left</span> <span class="token keyword">join</span> stu_subject b <span class="token keyword">on</span> s<span class="token punctuation">.</span>sco_subid <span class="token operator">=</span> b<span class="token punctuation">.</span>sub_id
<span class="token keyword">left</span> <span class="token keyword">join</span> stu_grade g <span class="token keyword">on</span> g<span class="token punctuation">.</span>gra_id <span class="token operator">=</span> t<span class="token punctuation">.</span>stu_classid
<span class="token keyword">where</span> g<span class="token punctuation">.</span>gra_id <span class="token operator">=</span> <span class="token number">2</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> t<span class="token punctuation">.</span>stu_name
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="行转列多关联" tabindex="-1"><a class="header-anchor" href="#行转列多关联" aria-hidden="true">#</a> 行转列多关联</h3><blockquote><p>行 ID: 1,2,3,4 关联 其他表ID 能进行多ID匹配</p></blockquote><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20230427202141997.png" alt="image-20230427202141997"></p><blockquote><p>案例:</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>purchase_inquiry_detail<span class="token punctuation">`</span></span> a
<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> purchase_inquiry pi <span class="token keyword">ON</span> pi<span class="token punctuation">.</span>order_number <span class="token operator">=</span> a<span class="token punctuation">.</span>order_number
<span class="token comment">-- 能进行多ID匹配 查询多条跟id吻合的数据</span>
<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> unit_info un <span class="token keyword">ON</span> FIND_IN_SET<span class="token punctuation">(</span> un<span class="token punctuation">.</span>id<span class="token punctuation">,</span> a<span class="token punctuation">.</span>unit_id <span class="token punctuation">)</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="多列或者查询函数" tabindex="-1"><a class="header-anchor" href="#多列或者查询函数" aria-hidden="true">#</a> 多列或者查询函数</h3><blockquote><p>即 多个条件对应 查询 一个值</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 表示: 查询列 a.spu_name 或者 a.spu_code 含有 mergesSearchTwo 的值 都会被查询出来</span>
<span class="token operator">and</span> concat_ws<span class="token punctuation">(</span><span class="token string">&#39;,&#39;</span><span class="token punctuation">,</span> a<span class="token punctuation">.</span>spu_name<span class="token punctuation">,</span> a<span class="token punctuation">.</span>spu_code<span class="token punctuation">)</span> <span class="token operator">like</span> concat<span class="token punctuation">(</span><span class="token string">&#39;%&#39;</span><span class="token punctuation">,</span> <span class="token comment">#{entity.mergesSearchTwo}, &#39;%&#39;)</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="递归查询" tabindex="-1"><a class="header-anchor" href="#递归查询" aria-hidden="true">#</a> 递归查询</h3><ul><li>MySQL - 8 递归 (WITH RECURSIVE)</li><li>每张表的列数 类型都必须一致</li></ul><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- mysql 递归 (WITH RECURSIVE)</span>
<span class="token comment">-- c 表: SELECT c.id, c.pid, c.district FROM `china` c where c.id = 13 只查湖北省及其所有子集</span>
<span class="token keyword">WITH</span> RECURSIVE cte <span class="token punctuation">(</span>id<span class="token punctuation">,</span> pid<span class="token punctuation">,</span> district<span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> c<span class="token punctuation">.</span>id<span class="token punctuation">,</span> c<span class="token punctuation">.</span>pid<span class="token punctuation">,</span> c<span class="token punctuation">.</span>district <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>china<span class="token punctuation">`</span></span> c 
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> c1<span class="token punctuation">.</span>id<span class="token punctuation">,</span> c1<span class="token punctuation">.</span>pid<span class="token punctuation">,</span> CONCAT<span class="token punctuation">(</span>ch<span class="token punctuation">.</span>district<span class="token punctuation">,</span> <span class="token string">&#39;/&#39;</span><span class="token punctuation">,</span> c1<span class="token punctuation">.</span>district<span class="token punctuation">)</span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>china<span class="token punctuation">`</span></span> c1
  <span class="token keyword">JOIN</span> cte ch <span class="token keyword">on</span> ch<span class="token punctuation">.</span>id <span class="token operator">=</span> c1<span class="token punctuation">.</span>pid
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 查询结果 (部分)</span>
<span class="token number">1602</span>	<span class="token number">190</span>	  湖北省<span class="token operator">/</span>随州<span class="token operator">/</span>广水市
<span class="token number">1603</span>	<span class="token number">191</span>	  湖北省<span class="token operator">/</span>天门<span class="token operator">/</span>天门市
<span class="token number">1604</span>	<span class="token number">192</span>	  湖北省<span class="token operator">/</span>咸宁<span class="token operator">/</span>咸安区
<span class="token number">1605</span>	<span class="token number">192</span>	  湖北省<span class="token operator">/</span>咸宁<span class="token operator">/</span>赤壁市
<span class="token number">1606</span>	<span class="token number">192</span>	  湖北省<span class="token operator">/</span>咸宁<span class="token operator">/</span>嘉鱼县
<span class="token number">1607</span>	<span class="token number">192</span>	  湖北省<span class="token operator">/</span>咸宁<span class="token operator">/</span>通城县
<span class="token number">1608</span>	<span class="token number">192</span>	  湖北省<span class="token operator">/</span>咸宁<span class="token operator">/</span>崇阳县
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><img src="https://cdn.jsdelivr.net/gh/LuJunandapai/ApaiImage@main/MaYun-PicGo/image-20230607150900959.png" alt="image-20230607150900959"></p><blockquote><h4 id="递归查询增加新列字段" tabindex="-1"><a class="header-anchor" href="#递归查询增加新列字段" aria-hidden="true">#</a> 递归查询增加新列字段</h4></blockquote><p>在递归查询的WITH recursive语句中增加一列字段，您可以使用ALTER TABLE语句来实现</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 以下是一个示例代码，演示如何向递归查询的WITH recursive语句中增加一列字段：</span>
<span class="token comment">-- 请注意，您需要将&quot;tb&quot;替换为您实际的表名，&quot;new_column_name&quot;替换为您要添加的新列的名称，&quot;new_column_value&quot;替换为新列的值或表达式</span>
<span class="token keyword">WITH</span> recursive tb <span class="token punctuation">(</span> <span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>name<span class="token punctuation">`</span></span><span class="token punctuation">,</span> p_id<span class="token punctuation">,</span> company_id<span class="token punctuation">,</span> new_column_name <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> <span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span><span class="token punctuation">,</span> NAME<span class="token punctuation">,</span> p_id<span class="token punctuation">,</span> company_id<span class="token punctuation">,</span> new_column_value
    <span class="token keyword">FROM</span> sanitary_system<span class="token punctuation">.</span>dim_spu_class
    <span class="token keyword">WHERE</span> p_id <span class="token operator">=</span> <span class="token number">0</span> <span class="token operator">AND</span> <span class="token identifier"><span class="token punctuation">`</span>status<span class="token punctuation">`</span></span> <span class="token operator">=</span> <span class="token number">1</span>
    <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
    <span class="token keyword">SELECT</span> c<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span><span class="token punctuation">,</span> CONCAT<span class="token punctuation">(</span>t<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>name<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token string">&#39;/&#39;</span><span class="token punctuation">,</span> c<span class="token punctuation">.</span>NAME<span class="token punctuation">)</span><span class="token punctuation">,</span> CONCAT<span class="token punctuation">(</span>t<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token string">&#39;,&#39;</span><span class="token punctuation">,</span> c<span class="token punctuation">.</span>code<span class="token punctuation">)</span><span class="token punctuation">,</span> c<span class="token punctuation">.</span>p_id<span class="token punctuation">,</span> c<span class="token punctuation">.</span>company_id<span class="token punctuation">,</span> new_column_value
    <span class="token keyword">FROM</span> sanitary_system<span class="token punctuation">.</span>dim_spu_class c
    <span class="token keyword">JOIN</span> tb t <span class="token keyword">ON</span> c<span class="token punctuation">.</span>p_id <span class="token operator">=</span> t<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tb<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>递归查询增加新列字段案例:</strong></p><ul><li>表默认字段为: code, name, p_id, company_id</li><li>需求: 新增列 pidstrs 存储 层级ID 如 1,2,3,4</li><li>外层表(显示字段名称), 联合外全两张表(函数产生的列) 字段必须数量必须一致 (pidstrs: 字符串新列)</li></ul><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
	a<span class="token punctuation">.</span><span class="token operator">*</span><span class="token punctuation">,</span>
	b<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>name<span class="token punctuation">`</span></span> <span class="token keyword">AS</span> dimSpuClassName<span class="token punctuation">,</span>
	IFNULL<span class="token punctuation">(</span>CONCAT<span class="token punctuation">(</span>b<span class="token punctuation">.</span>pidstrs<span class="token punctuation">,</span> <span class="token string">&#39;,&#39;</span><span class="token punctuation">,</span> a<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> pidstrs
<span class="token keyword">FROM</span>
	dim_spu_class a
	<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>
		<span class="token comment">-- 外层表(显示字段名称), 联合外全两张表(函数产生的列) </span>
        <span class="token comment">-- 字段必须数量必须一致 (pidstrs: 字符串新列)</span>
		<span class="token keyword">WITH</span> recursive tb <span class="token punctuation">(</span> <span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>name<span class="token punctuation">`</span></span><span class="token punctuation">,</span> pidstrs<span class="token punctuation">,</span> p_id<span class="token punctuation">,</span> company_id <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>
            <span class="token comment">-- 函数新增列 CONCAT(`code`) as pidstrs 合并多列为一列 主要是转换列类型</span>
			<span class="token keyword">SELECT</span> <span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span><span class="token punctuation">,</span> NAME<span class="token punctuation">,</span> CONCAT<span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span><span class="token punctuation">)</span> <span class="token keyword">as</span> pidstrs<span class="token punctuation">,</span> p_id<span class="token punctuation">,</span> company_id 
			<span class="token keyword">FROM</span> sanitary_system<span class="token punctuation">.</span>dim_spu_class <span class="token keyword">WHERE</span> p_id <span class="token operator">=</span> <span class="token number">0</span> <span class="token operator">AND</span> <span class="token identifier"><span class="token punctuation">`</span>status<span class="token punctuation">`</span></span> <span class="token operator">=</span> <span class="token number">1</span> 
		<span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
			<span class="token keyword">SELECT</span>
				c<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span><span class="token punctuation">,</span>
				CONCAT<span class="token punctuation">(</span> t<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>name<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token string">&#39;/&#39;</span><span class="token punctuation">,</span> c<span class="token punctuation">.</span>NAME <span class="token punctuation">)</span><span class="token punctuation">,</span>
            	<span class="token comment">-- 函数新增列</span>
				CONCAT<span class="token punctuation">(</span> t<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token string">&#39;,&#39;</span><span class="token punctuation">,</span> c<span class="token punctuation">.</span>code <span class="token punctuation">)</span> <span class="token keyword">as</span> pidstrs<span class="token punctuation">,</span>
				c<span class="token punctuation">.</span>p_id<span class="token punctuation">,</span>
				c<span class="token punctuation">.</span>company_id 
			<span class="token keyword">FROM</span> sanitary_system<span class="token punctuation">.</span>dim_spu_class c <span class="token keyword">JOIN</span> tb t <span class="token keyword">ON</span> c<span class="token punctuation">.</span>p_id <span class="token operator">=</span> t<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span> 
		<span class="token punctuation">)</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>  <span class="token keyword">FROM</span> tb 
		
	<span class="token punctuation">)</span> <span class="token keyword">AS</span> b <span class="token keyword">ON</span> a<span class="token punctuation">.</span>p_id <span class="token operator">=</span> b<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>code<span class="token punctuation">`</span></span> 
<span class="token keyword">WHERE</span> a<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>status<span class="token punctuation">`</span></span> <span class="token operator">=</span> <span class="token number">1</span> 
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><img src="https://cdn.jsdelivr.net/gh/LuJunandapai/ApaiImage@main/MaYun-PicGo/image-20230619101850664.png" alt="image-20230619101850664"></p><h2 id="mysql-技巧" tabindex="-1"><a class="header-anchor" href="#mysql-技巧" aria-hidden="true">#</a> MySql 技巧</h2><h3 id="时间自动添加-更新" tabindex="-1"><a class="header-anchor" href="#时间自动添加-更新" aria-hidden="true">#</a> 时间自动添加|更新</h3><p>详见: <a href="https://blog.csdn.net/qq_41942909/article/details/80749766" target="_blank" rel="noopener noreferrer">MySql数据库插入一条数据时，create_time字段自动添加为当前时间<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></p><blockquote><p>自动添加插入数据的时间</p></blockquote><ol><li>设置字段类型为datetime。(不建议使用<a href="https://so.csdn.net/so/search?q=timestamp&amp;spm=1001.2101.3001.7020" target="_blank" rel="noopener noreferrer">timestamp<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a>类型)</li><li>默认值填写为 CURRENT_TIMESTAMP （需要自己手动填写，注意单词拼写正确）</li><li>这样就达成了我们的目的，插入一条数据的时候，create_time字段会自动添加内容为插入数据的时间</li></ol><blockquote><p>自动添加修改数据的时间</p></blockquote><ol><li>设置字段类型为datetime。(不建议使用<a href="https://so.csdn.net/so/search?q=timestamp&amp;spm=1001.2101.3001.7020" target="_blank" rel="noopener noreferrer">timestamp<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a>类型)</li><li>默认值填写为 CURRENT_TIMESTAMP （需要自己手动填写，注意单词拼写正确）</li><li><strong>需要勾选默认值下方的 根据当前时间戳更新</strong>，修改一条数据的时候，create_time字段会自动添加内容为修改数据的时间</li></ol><p><img src="https://cdn.jsdelivr.net/gh/LuJunandapai/ApaiImage@main/MaYun-PicGo/nhifr8-0.png" alt="image-20221007121329040"></p><h2 id="避坑指南" tabindex="-1"><a class="header-anchor" href="#避坑指南" aria-hidden="true">#</a> 避坑指南:</h2><h3 id="数据库版本-分组报错" tabindex="-1"><a class="header-anchor" href="#数据库版本-分组报错" aria-hidden="true">#</a> 数据库版本 分组报错</h3><p>报错信息: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggre</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 删除 sql_mode = ONLY_FULL_GROUP_BY 这个设置</span>
<span class="token keyword">select</span> @<span class="token variable">@global.sql_mode</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> version<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
@<span class="token variable">@sql_mode</span><span class="token punctuation">;</span><span class="token keyword">SET</span> sql_mode<span class="token operator">=</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token keyword">REPLACE</span><span class="token punctuation">(</span>@<span class="token variable">@sql_mode</span><span class="token punctuation">,</span><span class="token string">&#39;ONLY_FULL_GROUP_BY&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;&#39;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment"># 修改全局</span>
<span class="token keyword">set</span> @<span class="token variable">@global.sql_mode</span> <span class="token operator">=</span> <span class="token string">&#39;&#39;</span><span class="token punctuation">;</span>
<span class="token keyword">set</span> @<span class="token variable">@global.sql_mode</span> <span class="token operator">=</span> <span class="token string">&#39;STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION&#39;</span><span class="token punctuation">;</span>
<span class="token comment"># 修改当前</span>
<span class="token keyword">set</span> @<span class="token variable">@sql_mode</span> <span class="token operator">=</span> <span class="token string">&#39;ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION&#39;</span><span class="token punctuation">;</span>

<span class="token comment">-- 方法二: 在mysql配置文件 my.ini 添加下面配置  最后再服务重启mysql即可</span>
sql_mode <span class="token operator">=</span> STRICT_TRANS_TABLES<span class="token punctuation">,</span>ERROR_FOR_DIVISION_BY_ZERO<span class="token punctuation">,</span>NO_ENGINE_SUBSTITUTION
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><div class="language-ini line-numbers-mode" data-ext="ini"><pre class="language-ini"><code><span class="token section"><span class="token punctuation">[</span><span class="token section-name selector">mysqld</span><span class="token punctuation">]</span></span>
<span class="token comment">## 设置server_id，同一局域网中需要唯一</span>
<span class="token key attr-name">server_id</span><span class="token punctuation">=</span><span class="token value attr-value">103</span>
<span class="token key attr-name">port</span><span class="token punctuation">=</span><span class="token value attr-value">3306</span>
<span class="token key attr-name">basedir</span><span class="token punctuation">=</span><span class="token value attr-value">C:\JavaFiles\mysql-8.0.19-winx64 # 这里替换成你自己的解压目录即可</span>
<span class="token key attr-name">datadir</span><span class="token punctuation">=</span><span class="token value attr-value">C:\JavaFiles\mysql-8.0.19-winx64\data   # 存储数据的文件</span>
<span class="token key attr-name">max_connections</span><span class="token punctuation">=</span><span class="token value attr-value">200</span>
<span class="token key attr-name">max_connect_errors</span><span class="token punctuation">=</span><span class="token value attr-value">10</span>
<span class="token key attr-name">character-set-server</span><span class="token punctuation">=</span><span class="token value attr-value">UTF8MB4</span>
<span class="token key attr-name">default-storage-engine</span><span class="token punctuation">=</span><span class="token value attr-value">INNODB</span>
<span class="token key attr-name">default_authentication_plugin</span><span class="token punctuation">=</span><span class="token value attr-value">mysql_native_password</span>
<span class="token key attr-name">sql_mode</span> <span class="token punctuation">=</span> <span class="token value attr-value">STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION</span>

<span class="token section"><span class="token punctuation">[</span><span class="token section-name selector">mysql</span><span class="token punctuation">]</span></span>
<span class="token key attr-name">default-character-set</span><span class="token punctuation">=</span><span class="token value attr-value">UTF8MB4</span>
<span class="token key attr-name">default-character-set</span><span class="token punctuation">=</span><span class="token value attr-value">utf8</span>

<span class="token section"><span class="token punctuation">[</span><span class="token section-name selector">client</span><span class="token punctuation">]</span></span>
<span class="token key attr-name">port</span><span class="token punctuation">=</span><span class="token value attr-value">3306</span>
<span class="token key attr-name">default-character-set</span><span class="token punctuation">=</span><span class="token value attr-value">utf8</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="left-join-增多数据条数" tabindex="-1"><a class="header-anchor" href="#left-join-增多数据条数" aria-hidden="true">#</a> LEFT JOIN 增多数据条数</h3><blockquote><p>问题的产生: 左连接即使已左表为主,但是右表对应的关系存在重复数据则会产生多余的数据</p></blockquote><p><img src="https://cdn.jsdelivr.net/gh/LuJunandapai/ApaiImage@main/MaYun-PicGo/fbmd75-0.png" alt=""></p><blockquote><p>解决方案</p></blockquote><p>参考: https://blog.csdn.net/lxy6904/article/details/114437506</p></div></div><footer class="page-meta"><!----><div class="meta-item last-updated"><span class="xicon-container left meta-item-label"><!--[--><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" class="xicon-icon" style="width:20px;height:20px;font-size:20px;color:inherit;"><path d="M26 4h-4V2h-2v2h-8V2h-2v2H6c-1.1 0-2 .9-2 2v20c0 1.1.9 2 2 2h20c1.1 0 2-.9 2-2V6c0-1.1-.9-2-2-2zm0 22H6V12h20v14zm0-16H6V6h4v2h2V6h8v2h2V6h4v4z" fill="currentColor"></path></svg><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Last Updated 2023-07-19 09:58:39<!--]--></span></span></div></footer><nav class="page-nav"><p class="hasPrev inner"><span class="page-nav-item prev"> ← MySql 数据库</span><!----></p></nav><!----></main><!--]--><div class="page-catalog-container"><h5 class="tip">文章层级目录</h5><ul><!--[--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#mysql-基础规范" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql 基础规范"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 基础规范<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#表-基础规范" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="表 基础规范"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->表 基础规范<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#sql-规范" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="SQL 规范"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->SQL 规范<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#java-符号的转译" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="Java 符号的转译"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java 符号的转译<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#模糊查询" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="模糊查询"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->模糊查询<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#范围查询" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="范围查询"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->范围查询<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#时间类函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="时间类函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->时间类函数<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#时间范围条件" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="时间范围条件"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->时间范围条件<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#获取现在时间函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="获取现在时间函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->获取现在时间函数<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#时间差列" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="时间差列"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->时间差列<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#时间差条件" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="时间差条件"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->时间差条件<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#时间转换格式" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="时间转换格式"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->时间转换格式<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#查询时间偏移数据" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="查询时间偏移数据"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->查询时间偏移数据<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#数学函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="数学函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->数学函数<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#绝对值" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="绝对值"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->绝对值<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#余数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="余数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->余数<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#不小于最小整数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="不小于最小整数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->不小于最小整数<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#四舍五入的整数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="四舍五入的整数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->四舍五入的整数<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#字符串函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="字符串函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->字符串函数<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#ascii-代码值" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="ASCII 代码值"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->ASCII 代码值<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#合并多列为一列" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="合并多列为一列"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->合并多列为一列<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#获取字符串长度" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="获取字符串长度"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->获取字符串长度<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#截取字符串" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="截取字符串"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->截取字符串<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#其他函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="其他函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->其他函数<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#判空列" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="判空列"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->判空列<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#双select查询成一表" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="双select查询成一表"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->双select查询成一表<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#联合外全-查询" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="联合外全 查询"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->联合外全 查询<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#条件判断后聚合列" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="条件判断后聚合列"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->条件判断后聚合列<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#行转列多关联" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="行转列多关联"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->行转列多关联<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#多列或者查询函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="多列或者查询函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->多列或者查询函数<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#递归查询" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="递归查询"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->递归查询<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#mysql-技巧" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql 技巧"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 技巧<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#时间自动添加-更新" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="时间自动添加|更新"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->时间自动添加|更新<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#避坑指南" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="避坑指南:"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->避坑指南:<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#数据库版本-分组报错" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="数据库版本 分组报错"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->数据库版本 分组报错<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySqlFunction.html#left-join-增多数据条数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="LEFT JOIN 增多数据条数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->LEFT JOIN 增多数据条数<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--]--></ul></div></div></div></div><!----><!----><!--]--></div>
    <script type="module" src="/assets/app-8a43a0f0.js" defer></script>
  </body>
</html>
